#!/bin/bash
# File Name: count_data_type.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-09 10:51:57
# Function: 
#########################################################################
db_name="db_cmdb"
mysql_user="moshan"
mysql_passwd="xxxxxx"
ip_port_list="/home/moshan/cmdb/ip_port.list"
host_list="192.168.2.142"
mysql_port=3306
col_all="/home/moshan/cmdb/col.log"
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db_name}"
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id;" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list} 2>/dev/null
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id where tdhgm.role_name='写节点'" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list}
for ((i=1;i<=$(wc -l < ${ip_port_list});i++)) #i  in $(awk '{print }' <<< "${ip_port_list}")
do
    host_list="$(awk -F: '{print $1}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    mysql_port="$(awk -F: '{print $2}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    col_data_type_count="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select concat(DATA_TYPE,':',count(*)) from information_schema.COLUMNS where table_schema not in ('mysql','information_schema','sys','performance_schema') group by DATA_TYPE; " 2>/dev/null)"
    echo "${col_data_type_count}"
done > ${col_all}
col_array=($(awk -F: '{print $1}' ${col_all}|sort -u|sed '/^$/d'))
for col in ${col_array[@]}
do
grep "^${col}:" ${col_all} |awk -F: -v sum=0 '{sum += $NF} END {print $1":"sum}'
done
